﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Xml;

/*
	Classes used to configure a sql profile session. There are three 
	areas to configure:
	- Trace Events  : only listen for the certain events to reduce the load imposed by the trace 
	- Report Colums : select only columns that contain data that helps
	- Column Filters : filter unimportant items from the trace in active databases

*/
namespace SqlServer.Profiler
{
	// Report Column configuration: 
	// the Id is defined by MS Sql server and comes from a system table 
	// (see ProfileConfiguration.Initialise() below)
	// ditto the title and datatype.
	//
	public class ProfilerColumnConfiguration
	{
		// For the source of the data see ProfileConfiguration.Initialise() below
		// 
		public ProfilerColumnConfiguration(int id, String title, String dataType)
		{
			this.Id = id;
			this.Title = title;
			this.DataType = dataType;
		}

		/// <summary>
		/// Use this version when storing user configurations. This will
		/// construct the config from the Xml of a node created by the ToXml()
		/// method below
		/// </summary>
		/// <param name="xml">Xml holding the config data</param>
		public ProfilerColumnConfiguration(String xml)
		{
			XmlDocument doc = new XmlDocument();
			doc.LoadXml(xml);
			String attValue = doc.DocumentElement.Attributes["Id"].Value;
			this.Id = Convert.ToInt32(attValue);
			this.Title = doc.DocumentElement.Attributes["Title"].Value;
			this.DataType = doc.DocumentElement.Attributes["DataType"].Value;
			if (doc.DocumentElement.Attributes["Filter"] != null)
				this.Filter = doc.DocumentElement.Attributes["Filter"].Value;
		}

		public int Id { get; set; }
		public string Title { get; set; }
		public string DataType { get; set; }
		/// <summary>
		/// Returns a description of the column shamelessly filched from the web.
		/// For use in profiler configuration forms to help the user unfamiliar 
		/// with the items.
		/// </summary>
		public String Description
		{
			get { return (helpText.ContainsKey(this.Id)) ? helpText[this.Id] : ""; }
		}
		/// <summary>
		/// Converts a database column type to a generic type 
		/// I = integer
		/// D = Date
		/// S = String
		/// </summary>
		public string Type
		{
			get
			{
				switch (this.DataType)
				{
					case "bigint":
					case "int":
						return "I";
					case "datetime":
						return "D";
					default:
						return "S";
				}
			}
		}
		public string Filter { get; set; }

		public override string ToString()
		{
			return this.Title;
		}
		/// <summary>
		/// Serialise the object into an Xml String for persisting user settings
		/// </summary>
		/// <returns></returns>
		public String ToXml()
		{
			return String.Format("<column Id=\"{0}\" Title=\"{1}\" DataType=\"{2}\" Filter=\"{3}\"/>", this.Id, this.Title, this.DataType, (String.IsNullOrEmpty(this.Filter)) ? "" : this.EncodeXml(this.Filter));
		}

		private string EncodeXml(string text)
		{
			return text.Replace("&", "&amp;");
		}
		/// <summary>
		/// Return a string representation of profiler trace data
		/// </summary>
		/// <param name="evt">Required purely to encapsulate the Duration hack from microseconds to milliseconds</param>
		/// <param name="column">The column whose data is to be fetched</param>
		/// <returns></returns>
		public static String GetEventData(ExpressProfiler.ProfilerEvent evt, ProfilerColumnConfiguration column)
		{
			switch (column.DataType)
			{
				case "bigint":
					{
						long lval = evt.GetLong(column.Id);
						// duration hack.
						if (column.Id == ExpressProfiler.ProfilerEventColumns.Duration) lval /= 1000;
						return lval.ToString();
					}
				case "int": return evt.GetInt(column.Id).ToString();
				case "datetime": return evt.GetDateTime(column.Id).ToString();
				case "image": return evt.GetByte(column.Id).ToString();
				case "uniqueidentifier": return evt.GetGuid(column.Id).ToString();
				case "nvarchar":
				case "text":
					return evt.GetString(column.Id);
				default:
					return evt.GetString(column.Id);
			}
		}
		/// <summary>
		/// Sets the profiler filter from the filter string attached to the column
		/// </summary>
		/// <param name="rdr">the profiler engine object</param>
		/// <param name="column">the column to filter on</param>
		public static void SetFilter(ExpressProfiler.RawTraceReader rdr, ProfilerColumnConfiguration column)
		{
			// Check whether set or not
			if (String.IsNullOrEmpty(column.Filter)) return;

			ProfileFilter f;
			if (ProfileFilter.TryParse(column.Filter, out f) == false) return; // Bad. Should throw 
			String item = f.Rhs;
			int filterOperator = MapOperator(f.Operator);
			if (column.DataType == "bigint" || column.DataType == "int")
			{
				long lval;
				if (long.TryParse(item, out lval))
				{
					// Duration hack: convert milliseconds to microseconds
					if (column.Id == ExpressProfiler.ProfilerEventColumns.Duration) lval *= 1000;
					rdr.SetFilter(column.Id, ExpressProfiler.LogicalOperators.AND, filterOperator, lval);
				}
			}
			else
			{
				// break up the RHS in case it is compound
				bool connectWithOr;
				String[] filterStrings = ProfileFilter.ParseStringFilter(f.Rhs, f.Operator, out connectWithOr);
				// connect first/only item with AND to filters on other columns
				int connector = ExpressProfiler.LogicalOperators.AND;
				foreach (String filterString in filterStrings)
				{
					item = (f.Operator == ProfileFilter.Op.like ||
							f.Operator == ProfileFilter.Op.notlike) ? "%" + filterString + "%" : filterString;
					rdr.SetFilter(column.Id, connector, filterOperator, item);
					if (connectWithOr)
						connector = ExpressProfiler.LogicalOperators.OR;
				}
			}
		}

		static int MapOperator(ProfileFilter.Op op)
		{
			return (op == ProfileFilter.Op.gteq) ? ExpressProfiler.ComparisonOperators.GreaterThanOrEqual :
					(op == ProfileFilter.Op.gt) ? ExpressProfiler.ComparisonOperators.GreaterThan :
					(op == ProfileFilter.Op.lteq) ? ExpressProfiler.ComparisonOperators.LessThanOrEqual :
					(op == ProfileFilter.Op.lt) ? ExpressProfiler.ComparisonOperators.LessThanOrEqual :
					(op == ProfileFilter.Op.eq) ? ExpressProfiler.ComparisonOperators.Equal :
					(op == ProfileFilter.Op.noteq) ? ExpressProfiler.ComparisonOperators.NotEqual :
					(op == ProfileFilter.Op.like) ? ExpressProfiler.ComparisonOperators.Like :
					(op == ProfileFilter.Op.notlike) ? ExpressProfiler.ComparisonOperators.NotLike :
					ExpressProfiler.ComparisonOperators.Equal;
		}
		static Dictionary<int, String> helpText = new Dictionary<int, string> {
			{1,"TextData: Text value dependent on the event class that is captured in the trace." },
			{2,"BinaryData: Binary value dependent on the event class captured in the trace. "},
			{3,"DatabaseID: ID of the database specified by the USE database statement, or the default database if no USE database statement is issued for a given connection. The value for a database can be determined by using the DB_ID function. "},
			{4,"TransactionID: System-assigned ID of the transaction. "},
			{5,"LineNumber: Contains the number of the line that contains the error. For events that involve Transact-SQL statements, like SP:StmtStarting, the LineNumber contains the line number of the statement in the stored procedure or batch. "},
			{6,"NTUserName: Microsoft® Windows® user name. "},
			{7,"NTDomainName: Windows domain to which the user belongs. "},
			{8,"HostName: Name of the client computer that originated the request. "},
			{9,"ClientProcessID: ID assigned by the client computer to the process in which the client application is running. "},
			{10,"ApplicationName: Name of the client application that created the connection to an instance of SQL Server. This column is populated with the values passed by the application rather than the displayed name of the program. "},
			{11,"LoginName: SQL Server login name of the client. "},
			{12,"SPID: Server Process ID assigned by SQL Server to the process associated with the client. "},
			{13,"Duration: Amount of elapsed time (in milliseconds) taken by the event. This data column is not populated by the Hash Warning event. "},
			{14,"StartTime Time at which the event started, when available. "},
			{15,"EndTime Time at which the event ended. This column is not populated for starting event classes, such as SQL:BatchStarting or SP:Starting. It is also not populated by the Hash Warning event. "},
			{16,"Reads Number of logical disk reads performed by the server on behalf of the event. This column is not populated by the Lock:Released event. "},
			{17,"Writes Number of physical disk writes performed by the server on behalf of the event. "},
			{18,"CPU Amount of CPU time (in milliseconds) used by the event. "},
			{19,"Permissions Represents the bitmap of permissions used by Security Auditing. "},
			{20,"Severity Severity level of an exception. "},
			{21,"EventSubClass Type of event subclass. This data column is not populated for all event classes. "},
			{22,"ObjectID System-assigned ID of the object. "},
			{23,"Success Success of the permissions usage attempt used for auditing. 1 = success 0 = failure "},
			{24,"IndexID ID for the index on the object affected by the event. To determine the index ID for an object, use the indid column of the sysindexes system table. "},
			{25,"IntegerData Integer value dependent on the event class captured in the trace. "},
			{26,"ServerName Name of the instance of SQL Server (either servername or servername\\instancename) being traced. "},
			{27,"EventClass Type of event class being recorded. "},
			{28,"ObjectType Type of object (such as table, function, or stored procedure). "},
			{29,"NestLevel The nesting level at which this stored procedure is executing. See @@NESTLEVEL (Transact-SQL). "},
			{30,"State Server state, in case of an error. "},
			{31,"Error Error number. "},
			{32,"Mode Lock mode of the lock acquired. This column is not populated by the Lock:Released event. "},
			{33,"Handle Handle of the object referenced in the event. "},
			{34,"ObjectName Name of object accessed. "},
			{35,"DatabaseName Name of the database specified in the USE database statement. "},
			{36,"FileName Logical name of the file name modified. "},
			{37,"OwnerName Owner name of the referenced object. "},
			{38,"RoleName Name of the database or server-wide role targeted by a statement. "},
			{39,"TargetUserName User name of the target of some action. "},
			{40,"DBUserName SQL Server database username of the client. "},
			{41,"LoginSid Security identifier (SID) of the logged-in user. "},
			{42,"TargetLoginName Login name of the target of some action. "},
			{43,"TargetLoginSid SID of the login that is the target of some action. "},
			{44,"ColumnPermissions Column-level permissions status; used by Security Auditing. "},
			{45,"LinkedServerName Name of the linked server. "},
			{46,"ProviderName Name of the OLE DB provider. "},
			{47,"MethodName Name of the OLE DB method. "},
			{48,"RowCounts Number of rows in the batch. "},
			{49,"RequestID ID of the request containing the statement. "},
			{50,"XactSequence A token to describe the current transaction. "},
			{51,"EventSequence Sequence number for this event. "},
			{52,"BigintData1 bigint value, which is dependent on the event class captured in the trace. "},
			{53,"BigintData2 bigint value, which is dependent on the event class captured in the trace. "},
			{54,"GUID GUID value, which is dependent on the event class captured in the trace. "},
			{55,"IntegerData2 Integer value, which is dependent on the event class captured in the trace. "},
			{56,"ObjectID2 ID of the related object or entity, if available. "},
			{57,"Type Integer value, which is dependent on the event class captured in the trace. "},
			{58,"OwnerID Type of the object that owns the lock. For lock events only. "},
			{59,"ParentName Name of the schema the object is within. "},
			{60,"IsSystem Indicates whether the event occurred on a system process or a user process. 1 = system 0 = user. "},
			{61,"Offset Starting offset of the statement within the stored procedure or batch. "},
			{62,"SourceDatabaseID ID of the database in which the source of the object exists. "},
			{63,"SqlHandle 64-bit hash based on the text of an ad hoc query or the database and object ID of an SQL object. This value can be passed to sys.dm_exec_sql_text() to retrieve the associated SQL text. "},
			{64,"SessionLoginName The login name of the user who originated the session. For example, if you connect to SQL Server using Login1 and execute a statement as Login2, SessionLoginName displays Login1, while LoginName displays Login2. This data column displays both SQL Server and Windows logins. "},
			};
	}
	/// <summary>
	/// Class to provide a list of available profiler event and column configuration information
	/// </summary>
	public class ProfilerEventsConfiguration
	{
		public ProfilerEventsConfiguration(int id, String title, int categoryId, String categoryName)
		{
			this.Id = id;
			this.Title = title;
			this.CategoryId = categoryId;
			this.CategoryName = categoryName;
		}

		/// <summary>
		/// Construct from Xml. An event node is built from an existing object using the ToXml() method
		/// </summary>
		/// <param name="xml">Xml from an event Xml node.</param>
		public ProfilerEventsConfiguration(String xml)
		{
			XmlDocument doc = new XmlDocument();
			doc.LoadXml(xml);
			String attValue = doc.DocumentElement.Attributes["Id"].Value;
			this.Id = Convert.ToInt32(attValue);
			this.Title = doc.DocumentElement.Attributes["Title"].Value;
			attValue = doc.DocumentElement.Attributes["CategoryId"].Value;
			this.CategoryId = Convert.ToInt32(attValue);
			this.CategoryName = doc.DocumentElement.Attributes["CategoryName"].Value;
		}
		/// <summary>
		/// Serialises a profiler event to Xml. The result is a single "event" element with attributes
		/// </summary>
		/// <returns>An XML element containing attributes set with the event details</returns>
		public String ToXml()
		{
			return String.Format("<event Id=\"{0}\" Title=\"{1}\" CategoryId=\"{2}\" CategoryName=\"{3}\" />", this.Id, this.Title, this.CategoryId, this.CategoryName);
		}

		public int Id { get; set; }
		public string Title { get; set; }
		public int CategoryId { get; set; }
		public string CategoryName { get; set; }

		/// <summary>
		/// returns an array of column Ids for columns that the trace populates with data when
		/// the event is recorded.
		/// </summary>
		/// <returns></returns>
		public int[] GetConnectedColumnIds()
		{
			int[,] bindings = ProfilerConfiguration.Bindings;

			List<int> eventColumnIds = new List<int>();
			int max = bindings.GetLength(0);
			for (int idx = 0; idx < max; ++idx)
			{
				if (bindings[idx, 0] == this.Id)
					eventColumnIds.Add(bindings[idx, 1]);
			}
			return eventColumnIds.ToArray();
		}
		/// <summary>
		/// Emits CategoryName:Title. For use in configuration dialog picklists
		/// </summary>
		/// <returns></returns>
		public override string ToString()
		{
			return String.Format("{0}:{1}", this.CategoryName, this.Title);
		}
	}
	/// <summary>
	/// Class to manage aspects of configuring a profile trace session. There are three elements
	/// a. Events
	/// b. Report columns
	/// c. Report column filters.
	/// This class creates static lists of the first two from an initial SQL query.
	/// It can also serialise and deserialise a configuration to and from Xml
	/// </summary>
	class ProfilerConfiguration
	{
		/// <summary>
		/// All available report columns
		/// </summary>
		public static ProfilerColumnConfiguration[] ColumnConfig;// {get; set;}
		/// <summary>
		/// All available events
		/// </summary>
		public static ProfilerEventsConfiguration[] EventConfig;// {get; set;}
		/// <summary>
		/// Event/Report column pair of Ids to indicate that a particular event generates data
		/// for the corresponding report column. This information can be used in configuration
		/// dialogs.
		/// </summary>
		public static int[,] Bindings;

		public ProfilerColumnConfiguration GetColumnConfigByIndex(int index)
		{
			return ColumnConfig[index];
		}

		public ProfilerColumnConfiguration GetColumnConfigById(int id)
		{
			foreach (ProfilerColumnConfiguration columnConfig in ColumnConfig)
				if (columnConfig.Id == id) return columnConfig;
			return null;
		}

		public ProfilerEventsConfiguration GetEventConfigByIndex(int index)
		{
			return EventConfig[index];
		}

		public ProfilerEventsConfiguration GetEventConfigById(int id)
		{
			foreach (ProfilerEventsConfiguration eventConfig in EventConfig)
				if (eventConfig.Id == id) return eventConfig;
			return null;
		}

		/// <summary>
		/// Initialise the static profiler information. 
		/// </summary>
		/// <param name="connectionString">Connection string of a database with sufficient rights to read the trace configuration tables</param>
		public void Initialise(String connectionString)
		{
			using (SqlConnection con = new SqlConnection(connectionString))
			using (SqlCommand command = con.CreateCommand())
			{
				command.CommandText = @"
select * from sys.trace_columns order by trace_column_id
";
				this.profileColumns = new DataTable();
				con.Open();
				SqlDataReader reader = command.ExecuteReader();
				this.profileColumns.Load(reader);
				command.CommandText = @"
select sc.category_id, sc.name as category_name, 
       trace_event_id, ev.name as event_name
  from sys.trace_categories sc 
  join sys.trace_events ev on sc.category_id = ev.category_id
 order by case when sc.name='TSQL' then ' ' else sc.name end, ev.name
";

				this.profileEvents = new DataTable();
				reader = command.ExecuteReader();
				this.profileEvents.Load(reader);

				command.CommandText = @"
select trace_event_id, trace_column_id from sys.trace_event_bindings
";
				DataTable dt = new DataTable();
				reader = command.ExecuteReader();
				dt.Load(reader);
				Bindings = new int[dt.Rows.Count, 2];
				int idx2 = 0;
				foreach (DataRow dr in dt.Rows)
				{
					Bindings[idx2, 0] = (Int16)dr[0];
					Bindings[idx2++, 1] = (Int16)dr[1];
				}

				con.Close();
			}
			ColumnConfig = new ProfilerColumnConfiguration[this.profileColumns.Rows.Count];
			int idx = 0;
			foreach (DataRow dr in this.profileColumns.Rows)
			{
				ColumnConfig[idx++] = new ProfilerColumnConfiguration((Int16)dr[0], dr[1].ToString(), dr[2].ToString());
			}

			EventConfig = new ProfilerEventsConfiguration[this.profileEvents.Rows.Count];
			idx = 0;
			foreach (DataRow dr in this.profileEvents.Rows)
			{
				EventConfig[idx++] = new ProfilerEventsConfiguration((Int16)dr[2], dr[3].ToString(), (Int16)dr[0], dr[1].ToString());
			}
		}
		/// <summary>
		/// Given an event return the report columns that may be populated when the event fires.
		/// </summary>
		/// <param name="eventConfig"></param>
		/// <param name="columns"></param>
		/// <returns></returns>
		public static int[] GetConnectedColumns(ProfilerEventsConfiguration eventConfig, ProfilerColumnConfiguration[] columns)
		{
			List<int> outList = new List<int>();
			int[] columnIds = eventConfig.GetConnectedColumnIds();
			foreach (ProfilerColumnConfiguration columnConfig in columns)
			{
				foreach (int id in columnIds)
				{
					if (columnConfig.Id == id)
					{
						outList.Add(id);
						break;
					}
				}
			}
			return outList.ToArray();
		}

		/// <summary>
		/// Helper method to format the filters set on a group of report columns
		/// into a single string. Used in config dialogs and eg. in the status bar 
		/// of the profiler window.
		/// </summary>
		/// <param name="columns"></param>
		/// <returns></returns>
		public static String FilterToString(ProfilerColumnConfiguration[] columns)
		{
			String report = "";
			foreach (ProfilerColumnConfiguration column in columns)
			{
				if (column != null && String.IsNullOrEmpty(column.Filter) == false)
				{
					ProfileFilter f;
					if (ProfileFilter.TryParse(column.Filter, out f))
					{
						if (report.Length > 0)
							report += " AND ";
						report += column.Title + " " + f.OperatorName + " " + f.Rhs;
					}
				}
			}
			return report;
		}
		/// <summary>
		/// Serialise a configuration of events and (filtered) columns to Xml for session or
		/// file persistence
		/// </summary>
		/// <param name="events"></param>
		/// <param name="columns"></param>
		/// <returns></returns>
		public static String ToXml(String key, String name, ProfilerEventsConfiguration[] events, ProfilerColumnConfiguration[] columns)
		{
			String xml = String.Format("<{0} name=\"{1}\">\n", key, name);
			xml += "<events>\n";
			foreach (ProfilerEventsConfiguration evt in events)
			{
				xml += evt.ToXml() + "\n";
			}
			xml += "</events>\n";
			xml += "<columns>\n";
			foreach (ProfilerColumnConfiguration column in columns)
			{
				xml += column.ToXml() + "\n";
			}
			xml += "</columns>\n";
			xml += String.Format("</{0}>", key);
			return xml;
		}
		/// <summary>
		/// Deserialise XML into events and report columns.
		/// </summary>
		/// <param name="xml"></param>
		/// <param name="events"></param>
		/// <param name="columns"></param>
		public static String LoadXml(String xml, out String name, out ProfilerEventsConfiguration[] events, out ProfilerColumnConfiguration[] columns)
		{
			String key = "";
			List<ProfilerEventsConfiguration> eventList = new List<ProfilerEventsConfiguration>();
			List<ProfilerColumnConfiguration> columnList = new List<ProfilerColumnConfiguration>();
			XmlDocument doc = new XmlDocument();
			doc.LoadXml(xml);
			// 
			key = doc.DocumentElement.Name;
			XmlAttribute att = doc.DocumentElement.Attributes["name"];
			name = (att == null) ? "" : att.Value;
			XmlNodeList nodes = doc.SelectNodes("//events/event");
			foreach (XmlNode node in nodes)
			{
				eventList.Add(new ProfilerEventsConfiguration(node.OuterXml));
			}
			nodes = doc.SelectNodes("//columns/column");
			foreach (XmlNode node in nodes)
			{
				columnList.Add(new ProfilerColumnConfiguration(node.OuterXml));
			}
			events = eventList.ToArray();
			columns = columnList.ToArray();

			return key;
		}

		DataTable profileColumns;
		DataTable profileEvents;
	}
}
